package main

import (
	"fmt"
	"log"

	_ "github.com/go-sql-driver/mysql"
	"github.com/jmoiron/sqlx"
)

var db *sqlx.DB

//sqlx 是对 sql 进行的一层封装
func initializeDatabase() (err error) {
	//data source name
	//用户名@密码@协议(ip:port)/数据库
	dsn := "root:123456@tcp(gincloud:3306)/gin_demo?charset=utf8mb4&parseTime=True&loc=Local"
	db, err = sqlx.Connect("mysql", dsn)
	if err != nil {
		fmt.Println("connect 2 database failed...")
		return
	}

	//这里可以不用ping了, sqlx 进行了处理

	//最大连接数
	db.SetMaxOpenConns(10)
	//最大闲置连接数
	db.SetMaxIdleConns(10)
	return nil
}

type user struct {
	//如果需要返回前端, 那么首字母需要大写(小写为私有)
	//前端展示效果可以通过 `json:"id"` 来处理
	//使用 sqlx 必须大写, 不然无法进行反射
	//db 指定数据库的字段名(如不指定则默认首字母小写)
	Id   uint64 `db:"id"`
	Name string `db:"name"`
	Age  uint32 `db:"age"`
}

func queryNamedQuery() {
	//使用 :id 代替占位符
	sqlStr := "select * from student where id = :id"
	//NamedQuery
	rows, err := db.NamedQuery(sqlStr, map[string]interface{}{"id": 2})
	if err != nil {
		log.Printf("NamedQuery failed err : %v\n", err)
	}
	defer rows.Close()
	for rows.Next() {
		var u user
		if err = rows.StructScan(&u); err != nil {
			log.Printf("StructScan failed err : %v\n", err)
		}
		fmt.Println("u=", u)
	}
}

func queryNamedQuery2() {
	//使用结构体代替map
	tmpUser := user{
		Id: 2,
	}
	//使用 :id 代替占位符
	sqlStr := "select * from student where id = :id"
	//NamedQuery
	rows, err := db.NamedQuery(sqlStr, tmpUser)
	if err != nil {
		log.Printf("NamedQuery failed err : %v\n", err)
	}
	defer rows.Close()
	for rows.Next() {
		var u user
		if err = rows.StructScan(&u); err != nil {
			log.Printf("StructScan failed err : %v\n", err)
		}
		fmt.Println("u=", u)
	}
}

func batchInsert() {
	users := []user{
		{Name: "2A", Age: 29},
		{Name: "2B", Age: 27},
		{Name: "2C", Age: 25},
	}
	//使用 :id 代替占位符
	sqlStr := "insert into student (name,age) values (:name,:age)"
	//NamedQuery
	_, err := db.NamedExec(sqlStr, users)
	if err != nil {
		log.Printf("NamedExec failed err : %v\n", err)
	}
	fmt.Println("success")
}

func main() {
	//连接数据库
	if err := initializeDatabase(); err != nil {
		panic(err)
	}
	fmt.Println("connected")

	queryNamedQuery()
	queryNamedQuery2()
	batchInsert()
}
